import xlrd
import xlwt
from openpyxl import load_workbook
import sg_panel
from datetime import datetime
import os


class dictionary():  #词典
    def branch(idict,fileName1):  #导出数据
        iD=dictionary.single(idict)
        for k,v in iD.items():
            print(k)
            for k1,v1 in v.items():
                print(k1)
                xlw=xlwt.Workbook()#初始化创建避免重写
                sheet1=xlw.add_sheet(u'4-1.计量箱与电能表的关系',cell_overwrite_ok=True)
                os.makedirs(fileName1+"/"+k+"/"+v1[0][7]+"-"+v1[0][6])
                for i in range(len(v1)):
                    sheet1.write(i,0,i+1)
                    sheet1.write(i,1,v1[i][1])
                    sheet1.write(i,2,v1[i][2])
                    sheet1.write(i,3,v1[i][3])
                    sheet1.write(i,4,v1[i][4])
                    sheet1.write(i,5,v1[i][5])
                    sheet1.write(i,6,v1[i][6])
                    sheet1.write(i,7,v1[i][7])
                    sheet1.write(i,8,v1[i][8])
                    sheet1.write(i,9,v1[i][9])
                    sheet1.write(i,10,v1[i][10])
                xlw.save(fileName1+"/"+k+"/"+v1[0][7]+"-"+v1[0][6]+"/"+v1[0][7]+"-"+v1[0][6]+".xls")
        return "导出成功"
    def whole(excel,fileName1,Max):#导入
        filename = fileName1
        wb = load_workbook(filename)
        ws = wb.active
        row=ws.max_row
        row+=1
        for i in range(len(excel)):
            ws["A"+str(row+i)] =str(Max+i)
            ws["B"+str(row+i)] =excel[i][1]
            ws["C"+str(row+i)] =excel[i][2]
            ws["D"+str(row+i)] =excel[i][3]
            ws["E"+str(row+i)] =excel[i][4]
            ws["F"+str(row+i)] =excel[i][5]
            ws["G"+str(row+i)] =excel[i][6]
            ws["H"+str(row+i)] =excel[i][7]
            ws["I"+str(row+i)] =excel[i][8]
            ws["J"+str(row+i)] =excel[i][9]
            ws["K"+str(row+i)] =excel[i][10]
        wb.save(filename)
        return "导入成功"
    def single(group):#分类
        c=group
        iDict={}
        for i in range(len(c)):
            row_list1=c[i][10]
            row_list2=c[i][6] 
            if not row_list1 in "":  #判断是否重复
                if not row_list2 in "":  
                    iDict.setdefault(row_list1,{})[row_list2]=[]
        for x in range(len(c)):
            row_list1=c[x][10]
            row_list2=c[x][6]
            iDict[row_list1][row_list2].append(c[x])
        return iDict          
    def establish(Route):  #读取Excel
        xls=xlrd.open_workbook(Route)  #加载Excel
        sh1=xls.sheet_names()#读取全部sheet页
          
        #检查格式
        Label=["序号","计量箱编号","安装地址","电表编号","行","列","台区编号","台区名称","用户编号","用户名称","管理单位"]
        if "4-1.计量箱与电能表的关系" in sh1:
            sh=xls.sheet_by_name("4-1.计量箱与电能表的关系")  #读取sheet页
            row1=sh.row_values(0)
            for i in range(len(Label)):
                if Label[i]!=row1[i]:
                    return "格式不对"
        else:
            return "没有此页"
       
        nrows=sh.nrows  #获取行数
        group=[] 
        for i in range(1,nrows):
            list_row=sh.row_values(i)
            group.append(list_row)
        return group


##start = round(time.time()*1000)#计时开始
##start_ = datetime.utcnow()
###f = open("e:\\test.txt", "r")
###c=eval(f.read())
###dictionary.create("","2000051240","","")
###f.close()
###dictionary.establish()
##start1 = round(time.time()*1000)
##start1_ = datetime.utcnow()
##s=(start1_-start_)  #计时结束
##print("完成: ",s)


